package Dm

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/parDef"
	"db2s/ref"
	"math"
	"strconv"
	"strings"
)

type constraintTypeMeS struct {
	NameB       string `json:"name"`
	ConstraintB string `json:"constraint"`
}
type indexColumnMeS struct {
	SchemaName string `json:"databaseName"`
	TableName  string `json:"tableName"`
	IndexName  string `json:"indexName"`
	ColumnName string `json:"columnName"`
	ColumnSeq  string `json:"columnSeq"`
}
type indexCategoryMe struct {
	SchemaName    string `json:"databaseName"`
	TableName     string `json:"tableName"`
	IndexName     string `json:"indexName"`
	NonUnique     string `json:"nonUnique"`
	Cardinality   int64  `json:"cardinality"`
	IndexCategory string `json:"indexCategory"`
}
type indexColumnDistinctMeS struct {
	ColumnSeq      string `json:"columnSeq"`
	ColumnDistinct string `json:"columnDistinct"`
}

var intType = map[string]string{"NUMBER": "int", "INTEGER": "int", "FLOAT": "int", "REAL": "int", "DOUBLE": "int", "DECIMAL": "int", "BINARY_FLOAT": "int", "BINARY_DOUBLE": "int"}
var timeType = map[string]string{"DATE": "time", "TIMESTAMP": "time", "INTERVAL YEAR": "time", "INTERVAL DAY": "time"}
var stringType = map[string]string{"CHAR": "string", "VARCHAR2": "string", "NCHAR": "string", "NVARCHAR2": "string"}
var typeCost = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}

func (im IndexMe) Set(parameter string, value any) (err error) {
	return ref.UpdateStructJsonValue(im, parameter, value)
}
func (im IndexMe) Result(object string) global.Return {
	switch object {
	default:
		return global.Return{}
	}
}
func (im IndexMe) ConstraintType(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[constraintType]"
	var res []constraintTypeMeS
	var execSql []string
	for _, v := range []string{"ALL_CONSTRAINTS", "DBA_CONSTRAINTS"} {
		execSql = append(execSql, fmt.Sprintf("select "+
			"CO.INDEX_NAME as \"name\", \n"+
			"CO.CONSTRAINT_TYPE as \"constraint\" \n"+
			"from "+
			"%v \"CO\" "+
			"where OWNER IN ('%v') AND TABLE_NAME IN ('%v')",
			v,
			parameter.Object.Schema,
			parameter.Object.Table))
	}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("constraintType", err)
		return
	}
	if result.Result == nil {
		return
	}
	var p = make(map[string]string)
	for _, v := range result.Result.([]map[string]any) {
		vv := mapAnyToString(v)
		name, ok1 := vv["name"]
		constraint, ok2 := vv["constraint"]
		if ok1 && ok2 {
			p[name] = constraint
		}
		var dd = constraintTypeMeS{}
		if err = mapStringToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("constraintType", err)
			return
		}
		res = append(res, dd)
	}
	result.Result, result.InsideResult = p, res
	return
}
func (im IndexMe) indexColumn(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[indexColumn]"
	var res []indexColumnMeS
	var execSql []string
	for _, v := range []string{"ALL_IND_COLUMNS", "DBA_IND_COLUMNS"} {
		execSql = append(execSql, fmt.Sprintf("select "+
			"IC.TABLE_OWNER AS \"databaseName\","+
			"IC.TABLE_NAME as \"tableName\","+
			"IC.INDEX_NAME AS \"indexName\","+
			"IC.COLUMN_POSITION AS \"columnSeq\","+
			"IC.COLUMN_NAME as \"columnName\" "+
			"from "+
			"\"%v\" \"IC\" "+
			"WHERE "+
			"IC.TABLE_OWNER IN ('%v') AND IC.TABLE_NAME IN ('%v') "+
			"order by IC.INDEX_NAME,IC.COLUMN_POSITION", v, parameter.Object.Schema, parameter.Object.Table))
	}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("indexColumn", err)
		return
	}
	if result.Result == nil {
		return
	}
	var p []map[string]string
	for _, v := range result.Result.([]map[string]interface{}) {
		vv := mapAnyToString(v)
		p = append(p, vv)
		var dd = indexColumnMeS{}
		if err = mapStringToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("indexColumn", err)
			return
		}
		res = append(res, dd)
	}
	result.Result, result.InsideResult = p, res
	return
}
func (im IndexMe) indexCategory(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[indexCategory]"
	var res []indexCategoryMe
	var execSql []string
	for _, v := range []string{"ALL_INDEXES", "DBA_INDEXES"} {
		execSql = append(execSql, fmt.Sprintf("SELECT "+
			"I.TABLE_OWNER AS \"databaseName\","+
			"I.TABLE_NAME as \"tableName\","+
			"I.INDEX_NAME as \"indexName\","+
			"I.UNIQUENESS AS \"nonUnique\","+
			"I.DISTINCT_KEYS AS \"cardinality\","+
			"I.PARTITIONED AS \"indexCategory\" "+
			"FROM \"%v\" \"I\" "+
			"WHERE I.TABLE_OWNER IN ('%v') AND I.TABLE_NAME IN ('%v')",
			v, parameter.Object.Schema, parameter.Object.Table))
	}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("indexCategory", err)
		return
	}
	if result.Result == nil {
		return
	}
	var p []map[string]any
	for _, v := range result.Result.([]map[string]any) {
		vv := mapNullActive(v)
		if vvp, ok := vv["indexCategory"]; ok {
			switch vvp {
			case "NO":
				vv["indexCategory"] = "global"
			case "YES":
				vv["indexCategory"] = "local"
			}
		}
		p = append(p, vv)
		var dd = indexCategoryMe{}
		if err = ref.MapToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("indexCategory", err)
			return
		}
		res = append(res, dd)
	}
	result.Result, result.InsideResult = p, res
	return
}
func (im IndexMe) nameColumnBase(parameter parDef.Parameter) (colMeta, constraintMeta, columnMeta, categoryMeta global.Return, err error) {
	cm := ColumnMe{}
	if colMeta, err = cm.Meta(parameter); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	parameter1 := parameter
	parameter1.Meta = colMeta.Result
	if colMeta, err = cm.MetaMap(parameter1); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if constraintMeta, err = im.ConstraintType(parameter); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if columnMeta, err = im.indexColumn(parameter); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if categoryMeta, err = im.indexCategory(parameter); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	return
}
func (im IndexMe) constraintBase(constraintMeta global.Return, indexName string) (constraintType string) {
	if constraintMeta.Result == nil {
		return
	}
	for _, cons := range constraintMeta.InsideResult.([]constraintTypeMeS) {
		if indexName == cons.NameB {
			constraintType = cons.ConstraintB
			return
		}
	}
	return
}
func (im IndexMe) indexColumnBase(colMeta, indexColumnMeta global.Return, indexName string) (res []parDef.IdxSubColumnMe) {
	if indexColumnMeta.Result == nil {
		return
	}
	for _, column := range indexColumnMeta.InsideResult.([]indexColumnMeS) {
		if indexName == column.IndexName {
			var dd = parDef.IdxSubColumnMe{ColumnName: column.ColumnName}
			for _, columnInfo := range colMeta.Result.([]parDef.ColMetaMapS) {
				if columnInfo.ColumnName == column.ColumnName {
					dd.IndexSeq = fmt.Sprintf("%v", column.ColumnSeq)
					dd.ColumnSeq = fmt.Sprintf("%v", columnInfo.ColumnSeq)
					dd.ColumnNull = columnInfo.ColNullMap
					dd.ColumnType = columnInfo.Type.ColTypeMap
					dd.Collation = columnInfo.CollationName
					res = append(res, dd)
					break
				}
			}
		}
	}
	return
}

func (im IndexMe) newColumnInit(allIndex global.Return) (res []parDef.IndexColumnMe) {
	if allIndex.Result == nil {
		return
	}
	for _, v := range allIndex.InsideResult.([]indexCategoryMe) {
		res = append(res, parDef.IndexColumnMe{
			Schema:        v.SchemaName,
			Table:         v.TableName,
			IndexName:     v.IndexName,
			IndexCategory: v.IndexCategory,
			NonUnique:     v.NonUnique,
			Cardinality:   v.Cardinality,
		})
	}
	return
}
func (im IndexMe) columnKey(vv parDef.IndexColumnMe, constraintMeta global.Return) (result parDef.IndexColumnMe) {
	result = vv
	switch strings.TrimSpace(im.constraintBase(constraintMeta, vv.IndexName)) {
	case "P":
		result.ColumnKey = "P"
		result.IndexName = "PRIMARY"
	case "U":
		result.ColumnKey = "U"
	default:
		if strings.EqualFold(vv.NonUnique, "UNIQUE") {
			result.ColumnKey = "U"
		} else {
			result.ColumnKey = "I"
		}
	}
	return
}

func (im IndexMe) ColumnDistinct(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[columnDistinct]"
	var execSql []string
	var res []indexColumnDistinctMeS
	for _, v := range []string{"ALL_OBJECTS", "DBA_OBJECTS"} {
		execSql = append(execSql, fmt.Sprintf("select "+
			"S.COLID+1 AS \"columnSeq\","+
			"S.N_DISTINCT AS \"columnDistinct\""+
			"from SYSSTATS S "+
			"LEFT JOIN "+
			"%v B ON B.OBJECT_ID = S.ID "+
			"WHERE B.OWNER ='%v' AND "+
			"B.OBJECT_NAME='%v' "+
			"AND S.COLID >=0 "+
			"order by S.COLID",
			v, parameter.Object.Schema, parameter.Object.Table))
	}
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil {
		err = ref.ErrAddPrintf("columnDistinct", err)
		return
	}
	if result.Result == nil {
		return
	}
	var p = make(map[string]any)
	for _, v := range result.Result.([]map[string]any) {
		vv := mapNullActive(v)
		columnSeq, ok1 := vv["columnSeq"]
		columnDistinct, ok2 := vv["columnDistinct"]
		if ok1 && ok2 {
			p[fmt.Sprintf("%v", columnSeq)] = columnDistinct
		}
		var dd = indexColumnDistinctMeS{}
		if err = ref.MapToStruct(vv, &dd); err != nil {
			err = ref.ErrAddPrintf("columnDistinct", err)
			return
		}
		res = append(res, dd)
	}
	result.Result, result.InsideResult = p, res
	return
}
func (im IndexMe) NameColumn(parameter parDef.Parameter) (result global.Return, err error) {
	var colMeta, constraintMeta, indexColumnMeta, allIndex global.Return
	if colMeta, constraintMeta, indexColumnMeta, allIndex, err = im.nameColumnBase(parameter); err != nil {
		err = ref.ErrAddPrintf("NameColumn", err)
		return
	}
	res := im.newColumnInit(allIndex)
	var resStr []parDef.IndexColumnMe
	for _, vv := range res {
		var newNameColumnMe = vv
		vv.ColumnMate = im.indexColumnBase(colMeta, indexColumnMeta, vv.IndexName)
		if len(vv.ColumnMate) == 0 {
			continue
		}
		newNameColumnMe = im.columnKey(vv, constraintMeta)
		resStr = append(resStr, newNameColumnMe)
	}
	result.Result = resStr
	return
}

// 按照索引离散度倒序排序（离散度最多两个列）
func (im IndexMe) cardinalityReverseSort(IIData []parDef.IndexColumnMe, Sum int64) (result []string) {
	var cardinality = make(map[string]int64)
	for _, v := range IIData {
		if Sum > 0 && v.Cardinality > 0 && v.Cardinality*100/Sum > 30 {
			cardinality[v.IndexName] = v.Cardinality
		}
	}
	result = ref.MapValueReverseSort(cardinality)
	return
}
func specifyIndexNameToNameColumnMe(specifyIndex string, IIData []parDef.IndexColumnMe) (p parDef.IndexColumnMe) {
	for _, vv := range IIData {
		if strings.EqualFold(specifyIndex, vv.IndexName) {
			p = vv
			return
		}
	}
	return
}
func compoundIndexColumnHistogram(columnHis []parDef.IdxSubColumnMe, cDistinctMeta any) (hist []int64) {
	var err error
	for k, vv := range columnHis {
		if k > 1 {
			continue
		}
		if colHist, ok := cDistinctMeta.(map[string]any)[vv.ColumnSeq]; ok {
			var hv int64
			if hv, err = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", colHist)), 10, 64); err != nil {
				hist = append(hist, 0)
			} else {
				hist = append(hist, hv)
			}
		}
	}
	return
}
func (im IndexMe) columnCardinalityStandard(hist []int64, Sum int64) bool {
	for _, vv := range hist {
		if vv*100/Sum > 30 {
			return true
		}
	}
	return false
}
func (im IndexMe) innodbIndexPageAverageRowsSize(parameter parDef.Parameter) (result global.Return, err error) {
	var execSql []string
	var event = "[innodbIndexPageAverageRowsSize]"
	execSql = append(execSql, fmt.Sprintf("select "+
		"round(n_rows/clustered_index_size,0) "+
		"from "+
		"mysql.innodb_table_stats "+
		"where database_name='%v' and table_name='%v'", parameter.Object.Schema, parameter.Object.Table))
	if result, err = forExecQuerySql(event, parameter, ETL.String, execSql); err != nil {
		err = ref.ErrAddPrintf("innodbIndexPageAverageRowsSize", err)
		return
	}
	if result.Result, err = stringAnyToInt64(result); err != nil {
		err = ref.ErrAddPrintf("innodbIndexPageAverageRowsSize", err)
	}
	return
}

// 比较单列和两个列的直方图，选择合适的
func (im IndexMe) columnHistogram(parameter parDef.Parameter, IIData []parDef.IndexColumnMe, Sum int64) (result []string, err error) {
	var columnDistinctMeta global.Return
	if columnDistinctMeta, err = im.ColumnDistinct(parameter); err != nil {
		err = ref.ErrAddPrintf("columnHistogram", err)
		return
	}
	if columnDistinctMeta.Result == nil {
		return
	}
	var cost = make(map[string]any)
	for _, v := range IIData {
		hist := compoundIndexColumnHistogram(v.ColumnMate, columnDistinctMeta.Result)
		switch {
		case len(hist) == 1 && hist[0] > 0 && im.columnCardinalityStandard(hist, Sum):
			cost[v.IndexName] = math.Round((float64(Sum)/float64(hist[0]))*100) / 100
		case len(hist) > 1 && hist[0] > 0 && hist[1] > 0 && im.columnCardinalityStandard(hist, Sum):
			var re, factor float64 = 0, 1
			for _, h := range hist {
				re = factor * math.Round((float64(Sum)/float64(h))*100) / 100
				factor = re
			}
			cost[v.IndexName] = re
		}
	}
	result = ref.MapValueSort(cost)
	return
}

func typeSelection(c parDef.IdxSubColumnMe) (cost int) {
	for _, v := range []map[string]string{intType, timeType, stringType} {
		if t, ok := v[strings.ToUpper(strings.Split(c.ColumnType, "(")[0])]; ok {
			return typeCost[t]
		}
	}
	return 100
}
func specifyIndexColumnTypeCost(IIData []parDef.IdxSubColumnMe) (cost int) {
	for _, v := range IIData {
		cost += typeSelection(v)
	}
	return
}

func (im IndexMe) typeScreening(screeningIndex []string, IIData []parDef.IndexColumnMe) (result string) {
	var indexTypeCost = make(map[string]any)
	for _, v := range screeningIndex {
		p := specifyIndexNameToNameColumnMe(v, IIData)
		indexTypeCost[v] = specifyIndexColumnTypeCost(p.ColumnMate)
	}
	res := ref.MapValueSort(indexTypeCost)
	if len(res) >= 1 {
		result = res[0]
	}
	return
}
func (im IndexMe) KeyChoose(parameter parDef.Parameter) (result global.Return, err error) {
	var res, indexColumnQueryData global.Return
	c := SumF{}
	if res, err = c.Rows(parameter); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	Sum := res.Result.(int64)
	if indexColumnQueryData, err = im.NameColumn(parameter); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if indexColumnQueryData.Result == nil {
		return
	}
	var IIData = indexColumnQueryData.InsideResult.([]parDef.IndexColumnMe)
	var screeningIndex []string
	if screeningIndex, err = im.columnHistogram(parameter, IIData, Sum); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if len(screeningIndex) == 0 {
		screeningIndex = im.cardinalityReverseSort(IIData, Sum)
	}
	chooseIndex := im.typeScreening(screeningIndex, IIData)
	for _, v := range IIData {
		if strings.EqualFold(v.IndexName, chooseIndex) {
			if result.Result, err = ref.StructToMap(v); err != nil {
				err = ref.ErrAddPrintf("KeyChoose", err)
				return
			}
		}
	}
	return
}
func (im IndexMe) NameToColumnClip(parameter parDef.Parameter) (result map[string][]string, err error) {
	var metaBase MetaBaseInitResult
	result = make(map[string][]string)
	if parameter.Meta == nil {
		return
	}
	if metaBase, err = metaBaseInit(parameter.Meta); err != nil {
		err = ref.ErrAddPrintf("NameToColumnClip", err)
		return
	}
	for _, indexCol := range metaBase.indexMeta {
		result[strings.ToUpper(fmt.Sprintf("%v@%v", indexCol.IndexName, indexCol.ColumnKey))] = func() (res []string) {
			for _, p := range indexCol.ColumnMate {
				res = append(res, strings.ToUpper(p.ColumnName))
			}
			return
		}()
	}
	return
}
